const express = require('express');
const { promisePool } = require('../config/database');
const { createNoticeSchema, updateNoticeSchema } = require('../schema/notice');

const router = express.Router();

// 发布公告接口
router.post('/create', async (req, res) => {
  try {
    console.log('收到发布公告请求:', req.body);
    
    // 数据验证
    const { error, value } = createNoticeSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: '数据验证失败',
        errors: error.details.map(detail => detail.message)
      });
    }

    const {
      title,
      content,
      type,
      is_urgent,
      publisher_id,
      publisher_name,
      publish_dept
    } = value;

    // 检查发布人是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ? AND deleted_at IS NULL',
      [publisher_id]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '发布人不存在'
      });
    }

    // 插入公告数据
    const [result] = await promisePool.execute(
      `INSERT INTO notices (
        title, content, type, status, is_urgent, 
        publisher_id, publisher_name, publish_dept, publish_time, read_count
      ) VALUES (?, ?, ?, 'published', ?, ?, ?, ?, NOW(), 0)`,
      [title, content, type, is_urgent, publisher_id, publisher_name, publish_dept]
    );

    if (result.affectedRows === 0) {
      return res.status(500).json({
        success: false,
        message: '公告发布失败'
      });
    }

    // 获取新创建的公告信息
    const [newNotice] = await promisePool.execute(
      `SELECT 
        id, title, content, type, status, is_urgent,
        publisher_id, publisher_name, publish_dept, publish_time, read_count,
        created_at, updated_at
      FROM notices 
      WHERE id = ?`,
      [result.insertId]
    );

    console.log('公告发布成功:', newNotice[0]);

    res.status(201).json({
      success: true,
      message: '公告发布成功',
      data: {
        notice: newNotice[0]
      }
    });

  } catch (error) {
    console.error('发布公告错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 数据库约束错误
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(400).json({
        success: false,
        message: '数据重复，请检查输入内容'
      });
    }
    
    // 外键约束错误
    if (error.code === 'ER_NO_REFERENCED_ROW_2') {
      return res.status(400).json({
        success: false,
        message: '发布人不存在，请检查用户信息'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 更新公告接口
router.put('/update/:id', async (req, res) => {
  try {
    const { id } = req.params;
    console.log('收到更新公告请求:', { id, body: req.body });
    
    if (!id || isNaN(parseInt(id))) {
      return res.status(400).json({
        success: false,
        message: '公告ID无效'
      });
    }

    // 数据验证
    const { error, value } = updateNoticeSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: '数据验证失败',
        errors: error.details.map(detail => detail.message)
      });
    }

    // 检查公告是否存在
    const [notices] = await promisePool.execute(
      'SELECT id, title FROM notices WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (notices.length === 0) {
      return res.status(404).json({
        success: false,
        message: '公告不存在'
      });
    }

    // 构建更新字段
    const updateFields = [];
    const updateValues = [];
    
    if (value.title !== undefined) {
      updateFields.push('title = ?');
      updateValues.push(value.title);
    }
    
    if (value.content !== undefined) {
      updateFields.push('content = ?');
      updateValues.push(value.content);
    }
    
    if (value.type !== undefined) {
      updateFields.push('type = ?');
      updateValues.push(value.type);
    }
    
    if (value.is_urgent !== undefined) {
      updateFields.push('is_urgent = ?');
      updateValues.push(value.is_urgent);
    }
    
    if (value.publisher_name !== undefined) {
      updateFields.push('publisher_name = ?');
      updateValues.push(value.publisher_name);
    }
    
    if (value.publish_dept !== undefined) {
      updateFields.push('publish_dept = ?');
      updateValues.push(value.publish_dept);
    }

    // 添加更新时间
    updateFields.push('updated_at = NOW()');
    
    // 添加ID到参数数组
    updateValues.push(id);

    if (updateFields.length === 1) { // 只有updated_at字段
      return res.status(400).json({
        success: false,
        message: '没有需要更新的字段'
      });
    }

    // 执行更新
    const updateQuery = `UPDATE notices SET ${updateFields.join(', ')} WHERE id = ?`;
    const [result] = await promisePool.execute(updateQuery, updateValues);

    if (result.affectedRows === 0) {
      return res.status(500).json({
        success: false,
        message: '更新公告失败'
      });
    }

    // 获取更新后的公告信息
    const [updatedNotice] = await promisePool.execute(
      `SELECT 
        id, title, content, type, status, is_urgent,
        publisher_id, publisher_name, publish_dept, publish_time, read_count,
        created_at, updated_at
      FROM notices 
      WHERE id = ?`,
      [id]
    );

    console.log('公告更新成功:', updatedNotice[0]);

    res.json({
      success: true,
      message: '公告更新成功',
      data: {
        notice: updatedNotice[0]
      }
    });

  } catch (error) {
    console.error('更新公告错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 数据库约束错误
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(400).json({
        success: false,
        message: '数据重复，请检查输入内容'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 获取公告列表接口
router.get('/list', async (req, res) => {
  try {
    console.log('收到获取公告列表请求:', req.query);
    
    const {
      page = 1,
      limit = 10,
      type = 'all',
      status = 'all',
      keyword = '',
      sortBy = 'created_at',
      sortOrder = 'desc'
    } = req.query;

    // 参数验证
    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    const offset = (pageNum - 1) * limitNum;
    

    if (pageNum < 1 || limitNum < 1 || limitNum > 100) {
      return res.status(400).json({
        success: false,
        message: '分页参数无效'
      });
    }

    // 构建查询条件
    let whereConditions = ['n.deleted_at IS NULL'];
    let queryParams = [];

    // 类型筛选
    if (type !== 'all') {
      whereConditions.push('n.type = ?');
      queryParams.push(type);
    }

    // 状态筛选
    if (status !== 'all') {
      whereConditions.push('n.status = ?');
      queryParams.push(status);
    }

    // 关键词搜索
    if (keyword.trim()) {
      whereConditions.push('(n.title LIKE ? OR n.content LIKE ?)');
      const searchKeyword = `%${keyword.trim()}%`;
      queryParams.push(searchKeyword, searchKeyword);
    }

    const whereClause = whereConditions.length > 0 ? 'WHERE ' + whereConditions.join(' AND ') : '';

    // 排序字段验证
    const allowedSortFields = ['created_at', 'updated_at', 'publish_time', 'read_count', 'title'];
    const sortField = allowedSortFields.includes(sortBy) ? sortBy : 'created_at';
    const sortDirection = sortOrder.toLowerCase() === 'asc' ? 'ASC' : 'DESC';

    // 查询公告列表 - 使用字符串拼接避免MySQL 8.0的LIMIT占位符问题
    const listQuery = `
      SELECT 
        n.id,
        n.title,
        n.content,
        n.type,
        n.status,
        n.is_urgent,
        n.publisher_id,
        n.publisher_name,
        n.publish_dept,
        n.publish_time,
        n.read_count,
        n.created_at,
        n.updated_at,
        u.name as publisher_real_name
      FROM notices n
      LEFT JOIN users u ON n.publisher_id = u.id
      ${whereClause}
      ORDER BY n.${sortField} ${sortDirection}
      LIMIT ${limitNum} OFFSET ${offset}
    `;


    const [notices] = await promisePool.execute(listQuery, queryParams);

    // 查询总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM notices n
      ${whereClause}
    `;
    
    const [countResult] = await promisePool.execute(countQuery, queryParams);
    console.log('查询总数结果:', countResult);
    const total = countResult[0].total;

    // 格式化数据
    const formattedNotices = notices.map(notice => ({
      id: notice.id,
      title: notice.title,
      content: notice.content,
      type: notice.type,
      status: notice.status,
      statusText: notice.status === 'published' ? '已发布' : '草稿',
      isUrgent: Boolean(notice.is_urgent),
      publisherId: notice.publisher_id,
      publisherName: notice.publisher_name,
      publisherRealName: notice.publisher_real_name,
      dept: notice.publish_dept,
      publishTime: notice.publish_time ? new Date(notice.publish_time).toISOString() : null,
      readCount: notice.read_count || 0,
      createTime: new Date(notice.created_at).toISOString(),
      updateTime: new Date(notice.updated_at).toISOString()
    }));

    console.log(`获取公告列表成功，共${total}条记录，返回${formattedNotices.length}条`);

    res.json({
      success: true,
      message: '获取公告列表成功',
      data: {
        notices: formattedNotices,
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: total,
          totalPages: Math.ceil(total / limitNum)
        }
      }
    });

  } catch (error) {
    console.error('获取公告列表错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 获取公告详情接口
router.get('/detail/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { 
      user_id, 
      device_type, 
      user_agent, 
      ip_address 
    } = req.query; // 从查询参数获取用户ID和设备信息
    console.log('收到获取公告详情请求:', { id, user_id, device_type, user_agent, ip_address });
    
    if (!id || isNaN(parseInt(id))) {
      return res.status(400).json({
        success: false,
        message: '公告ID无效'
      });
    }

    // 查询公告详情
    const [notices] = await promisePool.execute(
      `SELECT 
        n.id,
        n.title,
        n.content,
        n.type,
        n.status,
        n.is_urgent,
        n.publisher_id,
        n.publisher_name,
        n.publish_dept,
        n.publish_time,
        n.read_count,
        n.created_at,
        n.updated_at,
        u.name as publisher_real_name,
        u.role as publisher_role
      FROM notices n
      LEFT JOIN users u ON n.publisher_id = u.id
      WHERE n.id = ? AND n.deleted_at IS NULL`,
      [id]
    );

    if (notices.length === 0) {
      return res.status(404).json({
        success: false,
        message: '公告不存在'
      });
    }

    const notice = notices[0];
    let updatedReadCount = notice.read_count || 0;

    // 处理阅读记录（仅对已发布的公告且非发布者本人）
    if (notice.status === 'published' && user_id && !isNaN(parseInt(user_id))) {
      const userId = parseInt(user_id);
      
      // 检查是否为发布者本人
      if (notice.publisher_id !== userId) {
        // 检查是否已经记录过阅读
        const [existingLogs] = await promisePool.execute(
          'SELECT id FROM notice_read_logs WHERE notice_id = ? AND user_id = ?',
          [id, userId]
        );

        if (existingLogs.length === 0) {
          // 插入阅读记录
          console.log('插入阅读记录:', {
            notice_id: id,
            user_id: userId,
            device_type: device_type || 'unknown',
            user_agent: user_agent || null,
            ip_address: ip_address || null
          });
          
          console.log('原始参数:', {
            device_type: device_type,
            user_agent: user_agent,
            ip_address: ip_address
          });
          
          await promisePool.execute(
            `INSERT INTO notice_read_logs (
              notice_id, user_id, read_duration, ip_address, user_agent, device_type
            ) VALUES (?, ?, ?, ?, ?, ?)`,
            [id, userId, null, ip_address || null, user_agent || null, device_type || 'unknown']
          );

          // 更新公告阅读数量
          await promisePool.execute(
            'UPDATE notices SET read_count = read_count + 1 WHERE id = ?',
            [id]
          );

          // 获取更新后的阅读数量
          const [countResult] = await promisePool.execute(
            'SELECT read_count FROM notices WHERE id = ?',
            [id]
          );
          
          updatedReadCount = countResult[0].read_count;
          console.log('记录公告阅读成功:', { id, userId, readCount: updatedReadCount });
        }
      }
    }

    // 格式化数据
    const formattedNotice = {
      id: notice.id,
      title: notice.title,
      content: notice.content,
      type: notice.type,
      status: notice.status,
      statusText: notice.status === 'published' ? '已发布' : '草稿',
      isUrgent: Boolean(notice.is_urgent),
      publisherId: notice.publisher_id,
      publisherName: notice.publisher_name,
      publisherRealName: notice.publisher_real_name,
      publisherRole: notice.publisher_role,
      dept: notice.publish_dept,
      publishTime: notice.publish_time ? new Date(notice.publish_time).toISOString() : null,
      readCount: updatedReadCount,
      createTime: new Date(notice.created_at).toISOString(),
      updateTime: new Date(notice.updated_at).toISOString()
    };

    console.log('获取公告详情成功:', formattedNotice.id);

    res.json({
      success: true,
      message: '获取公告详情成功',
      data: {
        notice: formattedNotice
      }
    });

  } catch (error) {
    console.error('获取公告详情错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 获取公告统计接口
router.get('/stats', async (req, res) => {
  try {
    console.log('收到获取公告统计请求');
    
    // 查询统计数据
    const [stats] = await promisePool.execute(`
      SELECT 
        COUNT(*) as total_count,
        SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) as published_count,
        SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) as draft_count,
        SUM(CASE WHEN type = 'notice' THEN 1 ELSE 0 END) as notice_count,
        SUM(CASE WHEN type = 'finance' THEN 1 ELSE 0 END) as finance_count,
        SUM(CASE WHEN type = 'project' THEN 1 ELSE 0 END) as project_count,
        SUM(CASE WHEN type = 'policy' THEN 1 ELSE 0 END) as policy_count,
        SUM(CASE WHEN type = 'activity' THEN 1 ELSE 0 END) as activity_count,
        SUM(CASE WHEN type = 'urgent' THEN 1 ELSE 0 END) as urgent_count,
        SUM(CASE WHEN is_urgent = 1 THEN 1 ELSE 0 END) as urgent_notice_count,
        SUM(read_count) as total_read_count,
        AVG(read_count) as avg_read_count
      FROM notices 
      WHERE deleted_at IS NULL
    `);

    const statData = stats[0];

    // 格式化统计数据
    const formattedStats = {
      totalCount: statData.total_count || 0,
      publishedCount: statData.published_count || 0,
      draftCount: statData.draft_count || 0,
      noticeCount: statData.notice_count || 0,
      financeCount: statData.finance_count || 0,
      projectCount: statData.project_count || 0,
      policyCount: statData.policy_count || 0,
      activityCount: statData.activity_count || 0,
      urgentCount: statData.urgent_count || 0,
      urgentNoticeCount: statData.urgent_notice_count || 0,
      totalReadCount: statData.total_read_count || 0,
      avgReadCount: Math.round(statData.avg_read_count || 0)
    };

    console.log('获取公告统计成功:', formattedStats);

    res.json({
      success: true,
      message: '获取公告统计成功',
      data: {
        stats: formattedStats
      }
    });

  } catch (error) {
    console.error('获取公告统计错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 更新公告状态接口
router.put('/status/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    console.log('收到更新公告状态请求:', { id, status });
    
    if (!id || isNaN(parseInt(id))) {
      return res.status(400).json({
        success: false,
        message: '公告ID无效'
      });
    }

    if (!status || !['draft', 'published'].includes(status)) {
      return res.status(400).json({
        success: false,
        message: '状态参数无效，必须是draft或published'
      });
    }

    // 检查公告是否存在
    const [notices] = await promisePool.execute(
      'SELECT id, status FROM notices WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (notices.length === 0) {
      return res.status(404).json({
        success: false,
        message: '公告不存在'
      });
    }

    const currentNotice = notices[0];
    
    // 如果状态没有变化，直接返回成功
    if (currentNotice.status === status) {
      return res.json({
        success: true,
        message: '状态未发生变化',
        data: {
          notice: {
            id: parseInt(id),
            status: status
          }
        }
      });
    }

    // 更新公告状态
    const updateData = {
      status: status,
      updated_at: new Date()
    };

    // 如果是发布状态，设置发布时间；如果是草稿状态，保持原有发布时间不变
    let publishTime = null;
    if (status === 'published') {
      publishTime = new Date();
    } else if (status === 'draft') {
      // 撤回时保持原有发布时间，不设置为null
      const [existingNotice] = await promisePool.execute(
        'SELECT publish_time FROM notices WHERE id = ?',
        [id]
      );
      publishTime = existingNotice[0]?.publish_time || null;
    }

    const [result] = await promisePool.execute(
      `UPDATE notices 
       SET status = ?, publish_time = ?, updated_at = ?
       WHERE id = ?`,
      [status, publishTime, updateData.updated_at, id]
    );

    if (result.affectedRows === 0) {
      return res.status(500).json({
        success: false,
        message: '更新公告状态失败'
      });
    }

    console.log('更新公告状态成功:', { id, status });

    res.json({
      success: true,
      message: status === 'published' ? '公告发布成功' : '公告撤回成功',
      data: {
        notice: {
          id: parseInt(id),
          status: status,
          publishTime: publishTime ? publishTime.toISOString() : null,
          updateTime: updateData.updated_at.toISOString()
        }
      }
    });

  } catch (error) {
    console.error('更新公告状态错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 更新阅读时长接口
router.put('/read-duration/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { user_id, read_duration } = req.body;
    console.log('收到更新阅读时长请求:', { id, user_id, read_duration });
    
    if (!id || isNaN(parseInt(id))) {
      return res.status(400).json({
        success: false,
        message: '公告ID无效'
      });
    }

    if (!user_id || isNaN(parseInt(user_id))) {
      return res.status(400).json({
        success: false,
        message: '用户ID无效'
      });
    }

    if (!read_duration || isNaN(parseInt(read_duration))) {
      return res.status(400).json({
        success: false,
        message: '阅读时长无效'
      });
    }

    // 更新阅读时长
    console.log('准备更新阅读时长:', { 
      notice_id: id, 
      user_id: parseInt(user_id), 
      read_duration: parseInt(read_duration) 
    });
    
    const [result] = await promisePool.execute(
      'UPDATE notice_read_logs SET read_duration = ? WHERE notice_id = ? AND user_id = ?',
      [parseInt(read_duration), id, parseInt(user_id)]
    );

    console.log('数据库更新结果:', { 
      affectedRows: result.affectedRows, 
      changedRows: result.changedRows 
    });

    if (result.affectedRows === 0) {
      console.log('阅读记录不存在，无法更新');
      return res.status(404).json({
        success: false,
        message: '阅读记录不存在'
      });
    }

    console.log('更新阅读时长成功:', { id, user_id, read_duration });

    res.json({
      success: true,
      message: '阅读时长更新成功',
      data: {
        readDuration: parseInt(read_duration)
      }
    });

  } catch (error) {
    console.error('更新阅读时长错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 搜索公告接口
router.get('/search', async (req, res) => {
  try {
    console.log('收到搜索公告请求:', req.query);
    
    const {
      keyword = '',
      page = 1,
      limit = 20,
      type = 'all',
      sortBy = 'publish_time',
      sortOrder = 'desc'
    } = req.query;

    // 参数验证
    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    const offset = (pageNum - 1) * limitNum;
    
    if (pageNum < 1 || limitNum < 1 || limitNum > 100) {
      return res.status(400).json({
        success: false,
        message: '分页参数无效'
      });
    }

    if (!keyword.trim()) {
      return res.status(400).json({
        success: false,
        message: '搜索关键词不能为空'
      });
    }

    // 构建查询条件
    let whereConditions = ['n.deleted_at IS NULL', 'n.status = "published"'];
    let queryParams = [];

    // 关键词搜索 - 使用全文索引或LIKE查询
    const searchKeyword = `%${keyword.trim()}%`;
    whereConditions.push('(n.title LIKE ? OR n.content LIKE ?)');
    queryParams.push(searchKeyword, searchKeyword);

    // 类型筛选
    if (type !== 'all') {
      whereConditions.push('n.type = ?');
      queryParams.push(type);
    }

    const whereClause = 'WHERE ' + whereConditions.join(' AND ');

    // 排序字段验证
    const allowedSortFields = ['created_at', 'updated_at', 'publish_time', 'read_count', 'title'];
    const sortField = allowedSortFields.includes(sortBy) ? sortBy : 'publish_time';
    const sortDirection = sortOrder.toLowerCase() === 'asc' ? 'ASC' : 'DESC';

    // 查询公告列表
    const searchQuery = `
      SELECT 
        n.id,
        n.title,
        n.content,
        n.type,
        n.status,
        n.is_urgent,
        n.publisher_id,
        n.publisher_name,
        n.publish_dept,
        n.publish_time,
        n.read_count,
        n.created_at,
        n.updated_at,
        u.name as publisher_real_name
      FROM notices n
      LEFT JOIN users u ON n.publisher_id = u.id
      ${whereClause}
      ORDER BY n.${sortField} ${sortDirection}
      LIMIT ${limitNum} OFFSET ${offset}
    `;

    const [notices] = await promisePool.execute(searchQuery, queryParams);

    // 查询总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM notices n
      ${whereClause}
    `;
    
    const [countResult] = await promisePool.execute(countQuery, queryParams);
    const total = countResult[0].total;

    // 格式化数据
    const formattedNotices = notices.map(notice => ({
      id: notice.id,
      title: notice.title,
      content: notice.content,
      type: notice.type,
      status: notice.status,
      statusText: notice.status === 'published' ? '已发布' : '草稿',
      isUrgent: Boolean(notice.is_urgent),
      publisherId: notice.publisher_id,
      publisherName: notice.publisher_name,
      publisherRealName: notice.publisher_real_name,
      dept: notice.publish_dept,
      publishTime: notice.publish_time ? new Date(notice.publish_time).toISOString() : null,
      readCount: notice.read_count || 0,
      createTime: new Date(notice.created_at).toISOString(),
      updateTime: new Date(notice.updated_at).toISOString()
    }));

    // 构建搜索信息
    const searchInfo = {
      keyword: keyword.trim(),
      totalResults: total,
      searchTime: new Date().toISOString(),
      hasResults: total > 0
    };

    console.log(`搜索公告成功，关键词: "${keyword}", 共${total}条记录，返回${formattedNotices.length}条`);

    res.json({
      success: true,
      message: '搜索公告成功',
      data: {
        notices: formattedNotices,
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: total,
          totalPages: Math.ceil(total / limitNum)
        },
        searchInfo: searchInfo
      }
    });

  } catch (error) {
    console.error('搜索公告错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

// 删除公告接口
router.delete('/delete/:id', async (req, res) => {
  try {
    const { id } = req.params;
    console.log('收到删除公告请求:', id);
    
    if (!id || isNaN(parseInt(id))) {
      return res.status(400).json({
        success: false,
        message: '公告ID无效'
      });
    }

    // 检查公告是否存在
    const [notices] = await promisePool.execute(
      'SELECT id, title FROM notices WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (notices.length === 0) {
      return res.status(404).json({
        success: false,
        message: '公告不存在'
      });
    }

    // 软删除公告
    const [result] = await promisePool.execute(
      'UPDATE notices SET deleted_at = NOW() WHERE id = ?',
      [id]
    );

    if (result.affectedRows === 0) {
      return res.status(500).json({
        success: false,
        message: '删除公告失败'
      });
    }

    console.log('删除公告成功:', { id, title: notices[0].title });

    res.json({
      success: true,
      message: '删除公告成功',
      data: {
        notice: {
          id: parseInt(id),
          deletedAt: new Date().toISOString()
        }
      }
    });

  } catch (error) {
    console.error('删除公告错误:', error);
    
    // 数据库连接错误
    if (error.code === 'ECONNREFUSED' || error.code === 'ER_ACCESS_DENIED_ERROR') {
      return res.status(500).json({
        success: false,
        message: '数据库连接失败，请检查数据库配置'
      });
    }
    
    // 其他数据库错误
    if (error.code && error.code.startsWith('ER_')) {
      return res.status(500).json({
        success: false,
        message: '数据库操作失败',
        error: process.env.NODE_ENV === 'development' ? error.message : '服务器内部错误'
      });
    }
    
    // 通用错误处理
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : '未知错误'
    });
  }
});

module.exports = router;